package com.sunyard.utils.manage;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.sunyard.constant.msg.ConfCache;
import com.sunyard.entity.FileTypeName;
import com.sunyard.utils.sm2.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: excel解析工具类
 * @author: DUANYUBIN
 * @create: 2020-12-21 09:22
 **/
@Slf4j
public class ExcelUtils {

    /**
     * @param rowHandler 继承hutool的RowHandler行处理器 行处理中去处理excel数据
     * @param in         文件字节流
     * @description 利用hutool工具类，通过行处理器来处理excel数据
     */
    public static void readExcel(RowHandler rowHandler, InputStream in) throws Exception {

        long start = System.currentTimeMillis();
        ExcelUtil.readBySax(in, 0, rowHandler);
        long end = System.currentTimeMillis();
        log.info("读取时间" + (end - start));
    }

    /**
     * 导入商户号 仅供营销活动和手续费率优惠套餐绑定商户时使用
     *
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List<String> getMchntCdsFromFile(String filePath) throws Exception {
        try {
            List<String> mchntCds = new ArrayList<>();
            filePath = filePath.replace(ConfCache.getValue("amazon.accessKeyId"), "");
            byte[] bytes = EosFileUtils.downloadFileByte(filePath);
            String isE2007 = FileTypeName.FILE_TYPE_XLSX;

            ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007.equals(filePath.substring(filePath.indexOf(".") + 1))) {
                wb = new XSSFWorkbook(bis);
            } else {
                wb = new HSSFWorkbook(bis);
            }
            // 默认获取第一个表单内容
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum() + 1;
            /**
             * 第一行为标题，从第二行开始计数
             */
            for (int i = 1; i < count; i++) {
                // 获取当前行
                Row row = sheet.getRow(i);
                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                }
                String cellValue = row.getCell(0).getStringCellValue();
                if (null == cellValue || "".equals(cellValue)) {
                    continue;
                }
                mchntCds.add(cellValue);
            }
            return mchntCds;
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * Excel输出流
     *
     * @param type  输出格式 true xlsx false xls
     * @param rows  输入信息 通过 CollUtil.newArrayList(T 1,T 2...) 生成
     * @param i     首行标题 于title一起使用
     * @param title 输出标题
     * @param out   输出流
     */
    public static <T> void exportMapToExcel(Boolean type, ArrayList<T> rows, int i, String title, OutputStream out) throws Exception {
        ExcelWriter writer = null;
        if (type) {
            //创建xlsx格式的
            writer = ExcelUtil.getWriter(true);
        } else {
            // 通过工具类创建writer，默认创建xls格式
            writer = ExcelUtil.getWriter();
        }
        if (!StringUtil.isEmpty(title) && i != 0) {
            writer.merge(i, title);
        }
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        //out为OutputStream，需要写出到的目标流
        writer.flush(out);
        // 关闭writer，释放内存
        writer.close();
    }

    /**
     * Excel输出流
     *
     * @param type 输出格式 true xlsx false xls
     * @param rows 输入信息 通过 CollUtil.newArrayList(T 1,T 2...) 生成
     * @param out  输出流
     */
    public static <T> void exportMapToExcel(Boolean type, ArrayList<T> rows, OutputStream out) throws Exception {
        exportMapToExcel(true, rows, 0, "", out);
    }

    /**
     * 批量导入门店信息，且仅用于批量导入门店信息
     *
     * @param multipartFile
     * @return
     * @throws Exception
     */
    public static List<String> getShopMsFromFile(MultipartFile multipartFile) throws Exception {
        try {
            List<String> shopMs = new ArrayList<>();

            String filename = multipartFile.getOriginalFilename();
            int lastIndexOf = filename.lastIndexOf(".");
            String nameSuffix = filename.substring(lastIndexOf + 1);

//            filePath = filePath.replace(ConfCache.getValue("amazon.accessKeyId"), "");
//            byte[] bytes = EosFileUtils.downloadFileByte(filePath);
            String isE2007 = FileTypeName.FILE_TYPE_XLSX;

//            ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
            InputStream inputStream = multipartFile.getInputStream();
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007.equals(nameSuffix)) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            // 默认获取第一个表单内容
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum() + 1;
            /**
             * 第一行为标题，从第二行开始计数
             */
            DecimalFormat df = new DecimalFormat();
            for (int i = 1; i < count; i++) {
                // 获取当前行
                Row row = sheet.getRow(i);
                String ms = "";
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    if (j < (row.getLastCellNum() - 1)) {
                        if (ObjectUtil.isEmpty(row.getCell(j))) {
                            continue;
                        }
                        if (row.getCell(j).getCellTypeEnum().getCode() == 0) {
                            String tmp = df.format(row.getCell(j).getNumericCellValue());
                            String[] split = tmp.split(",");
                            String sTmp = "";
                            for (String s : split) {
                                sTmp = sTmp + s;
                            }
                            ms = ms + sTmp + ",";
                        } else if (row.getCell(j).getCellTypeEnum().getCode() == 1) {
                            ms = ms + row.getCell(j).getStringCellValue() + ",";
                        }
                    } else {
                        if (ObjectUtil.isEmpty(row.getCell(j))) {
                            continue;
                        }
                        if (row.getCell(j).getCellTypeEnum().getCode() == 0) {
                            String tmp = df.format(row.getCell(j).getNumericCellValue());
                            String[] split = tmp.split(",");
                            String sTmp = "";
                            for (String s : split) {
                                sTmp = sTmp + s;
                            }
                            ms = ms + sTmp;
                        } else if (row.getCell(j).getCellTypeEnum().getCode() == 1) {
                            ms = ms + row.getCell(j).getStringCellValue();
                        }
                    }
                }
                if (StringUtils.isEmpty(ms)) {
                    continue;
                }
                shopMs.add(ms);

            }
            return shopMs;
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw e;
        }
    }
}
